# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Boolean, DateTime
from sqlalchemy import Table, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import sessionmaker
import datetime

engine = create_engine('sqlite:///./blog.db', echo=True)
Base = declarative_base()

class usertable(Base):
    __tablename__='usertable'
    __table_args__ = {'sqlite_autoincrement':True}
    userid = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False, unique=True)
    passwd = Column(String, nullable=False)
    blog = relationship("blogtable",backref="usertable")
    comment = relationship("commenttable", backref="usertable")

class blogtable(Base):
    __tablename__='blogtable'
    __table_args__ = {'sqlite_autoincrement':True}
    blogid = Column(Integer, primary_key=True, autoincrement=True)
    userid = Column(Integer, ForeignKey('usertable.userid'))
    title = Column(String, nullable=False, index=True)
    text = Column(String, nullable=False)
    typeid = Column(Integer, ForeignKey('typetable.typeid'))
    created_date = Column(DateTime, nullable=False, default=datetime.datetime.utcnow)
    comment = relationship("commenttable", backref="blogtable")


class commenttable(Base):
    __tablename__='commenttable'
    __table_args__ = {'sqlite_autoincrement': True}
    commentid = Column(Integer, primary_key=True, autoincrement=True)
    blogid = Column(Integer, ForeignKey('blogtable.blogid'))
    comment_by_id = Column(Integer, ForeignKey('usertable.userid'))
    comment_text = Column(String, nullable=False)

class typetable(Base):
    __tablename__='typetable'
    __table_args__ = {'sqlite_autoincrement':True}
    typeid = Column(Integer, primary_key=True, autoincrement=True)
    typename = Column(String, nullable=False)
    typecount = Column(Integer, nullable=False, default=0)
    blog = relationship("blogtable", backref="typetable")
        

def create_table():
    Base.metadata.create_all(engine)

def init_typetable():

    type1 = typetable(
        
        typename = 'c/c++',
        typecount = 0
    )

    type2 = typetable(
        
        typename = 'python',
        typecount = 0
    )

    type3 = typetable(
        
        typename = 'java',
        typecount = 0
    )

    type4 = typetable(
        
        typename = 'linux',
        typecount = 0
    )

    type5 = typetable(
        
        typename = u'数据结构与算法',
        typecount = 0
    )
    qsession = get_session()
    qsession.add(type1)
    qsession.add(type2)
    qsession.add(type3)
    qsession.add(type4)
    qsession.add(type5)
    qsession.commit()

def get_session():
    Session = sessionmaker(bind=engine)
    session = Session()
    return session

